﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace PatientsManagement.Database
{
    static class VisitsData
    {
        static PatientsDataContext patients = new PatientsDataContext();

        public class PatientsShortInformation
        {
            public int ID { get; set; }
            public string Imię { get; set; }
            public string Nazwisko { get; set; }
            public string Pesel { get; set; }
            public int Wizyty { get; set; }
        }

        public static IQueryable<PatientsShortInformation> GetShortenedPatientsTable()
        {
            var query = (from patient in patients.Patients
                         select new PatientsShortInformation()
                         {
                             ID = patient.PatientID,
                             Imię = patient.Forename,
                             Nazwisko = patient.Surname,
                             Pesel = patient.Pesel,
                             Wizyty = (from visits in patients.Visits
                                       where visits.PatientID == patient.PatientID
                                       select visits).Count()
                         });
            return query;
        }

        public class PatientsVisitsInformation
        {
            public int ID_Wizyty { get; set; }
            public string Pesel { get; set; }
            public string Rozpoznanie { get; set; }
            public int ID_Recepty { get; set; }
            public string Data_Wizyty { get; set; }
        }

        public static IQueryable<PatientsVisitsInformation> GetPatientVisitsHistoryTable(int doctorID, int patientID)
        {
            var query = (from visit in patients.Visits
                         join diagnosisData in patients.DiagnosisDatas on visit.VisitID equals diagnosisData.VisitID
                         join diagnosis in patients.Diagnosis on diagnosisData.DiagnosisID equals diagnosis.DiagnosisID
                         join patient in patients.Patients on visit.PatientID equals patient.PatientID into patiendD
                         join prescription in patients.Prescriptions on visit.VisitID equals prescription.VisitID into pd
                         where visit.DoctorID == doctorID
                         where visit.PatientID == patientID
                         from preData in pd.DefaultIfEmpty()
                         from patData in patiendD.DefaultIfEmpty()
                         select new PatientsVisitsInformation()
                         {
                             ID_Wizyty = visit.VisitID,
                             Pesel = patData.Pesel,
                             Rozpoznanie = diagnosis.DiagnosisName,
                             ID_Recepty = preData.PrescriptionID,
                             Data_Wizyty = visit.VisitDate.ToShortDateString()
                         }).Distinct();
            return query;
        }

        internal static List<string> DiagnosisNames()
        {
            List<string> diagnosisList;
            diagnosisList = (from  diagnosis in patients.Diagnosis
                               select diagnosis.DiagnosisName).ToList<string>();
            return diagnosisList;
        }

        public static void AddVisit(int patientID,int doctorID,string diagnosisName,DateTime visitDate,string prescriptionNumber,bool addPrescription)
        {
            Visit visit = new Visit
            {
                PatientID = patientID,
                DoctorID = doctorID,
                VisitDate = visitDate
            };
            patients.Visits.InsertOnSubmit(visit);
            try
            {
                patients.SubmitChanges();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            int visitID = (from newVisit in patients.Visits
                          where newVisit.PatientID == patientID
                          orderby newVisit.VisitID descending
                          select newVisit.VisitID).First();
            DiagnosisData diagnosisData = new DiagnosisData
            {
                VisitID = visitID,
                DiagnosisID = GetDiagnosisID(diagnosisName)
            };
            patients.DiagnosisDatas.InsertOnSubmit(diagnosisData);

            if (!addPrescription)
            {
                Prescription prescription = new Prescription
                {
                    PrescriptionNumber = prescriptionNumber,
                    VisitID = visitID
                };
                patients.Prescriptions.InsertOnSubmit(prescription);
            }
            try
            {
                patients.SubmitChanges();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        public static void DeleteVisit(int visitID)
        {
            var singleVisit = (from visit in patients.Visits
                                 where visit.VisitID == visitID
                                 select visit).Single();
            patients.Visits.DeleteOnSubmit(singleVisit);
            try
            {
                patients.SubmitChanges();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        public static int GetDiagnosisID(string diagnosisName)
        {
            var query = (from diagnosis in patients.Diagnosis
                         where diagnosis.DiagnosisName == diagnosisName
                         select diagnosis).Single();
            return query.DiagnosisID;
        }

        public static string GetDiagnosisName(int visitID)
        {
            var query = (from diagnosis in patients.DiagnosisDatas
                         join diagnosisData in patients.Diagnosis on diagnosis.DiagnosisID equals diagnosisData.DiagnosisID
                         where diagnosis.VisitID == visitID
                         select diagnosis.Diagnosis.DiagnosisName).Single();
            return query;
        }

        public static DateTime GetVisitDate(int visitID)
        {
            var query = (from visit in patients.Visits
                         where visit.VisitID == visitID
                         select visit.VisitDate).Single();
            return query;
        }

        public static bool PrescriptionExists(int visitID)
        {
            var query = (from prescription in patients.Prescriptions
                         where prescription.VisitID == visitID
                         select prescription.PrescriptionNumber).ToList();
            return (query.Count > 0);
        }

        public static string PrescriptionNumber(int visitID)
        {
            var query = (from prescription in patients.Prescriptions
                         where prescription.VisitID == visitID
                         select prescription.PrescriptionNumber).Single();
            return query;
        }

        public static void SaveChanges(int visitID, string diagnosisName, DateTime visitDate,bool prescriptionNotAdd,string prescriptionNumber)
        {
            var singleVisit = (from visit in patients.Visits
                               where visit.VisitID == visitID
                               select visit).Single();
            singleVisit.VisitDate = visitDate;
            var singleDiagnosis = (from diagnosis in patients.DiagnosisDatas
                                join diagnosisValue in patients.Diagnosis on diagnosis.DiagnosisID equals diagnosisValue.DiagnosisID
                                where diagnosis.VisitID == visitID
                                select diagnosis).Single();
            singleDiagnosis.DiagnosisID = GetDiagnosisID(diagnosisName);
            if (PrescriptionExists(visitID))
            {
                var singlePrescription = (from prescription in patients.Prescriptions
                                          where prescription.VisitID == visitID
                                          select prescription).Single();
                if (prescriptionNotAdd)
                {
                    patients.Prescriptions.DeleteOnSubmit(singlePrescription);
                }
                else
                {
                    singlePrescription.PrescriptionNumber = prescriptionNumber.ToString();
                }
            }
            else
            {
                if (!prescriptionNotAdd)
                {
                    Prescription prescription = new Prescription
                    {
                        PrescriptionNumber = prescriptionNumber,
                        VisitID = visitID
                    };
                    patients.Prescriptions.InsertOnSubmit(prescription);
                }
            }
            try
            {
                patients.SubmitChanges();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

    }
}
